﻿Public Class ServicoDAO

    Dim objAcessoDados As AcessoDados = New AcessoDados(Config.StringConexao)

    Public Function ListaTodos() As List(Of Servico)
        Dim registros As DataSet = New DataSet
        registros = objAcessoDados.ExecutaSqlRetorno("select * from tb_servico")
        Return CriarListaCServ(registros)
    End Function

    Public Function Lista() As DataTable
        Dim registros As DataTable = New DataTable
        registros = objAcessoDados.ExecutaSqlRetornoDataTable("SELECT COD_SERV, DESC_SERV FROM TB_SERVICO")
        Return registros
    End Function

    Public Function CriarListaCServ(ByVal Registros As DataSet) As List(Of Servico)
        Dim lista As New List(Of Servico)
        Dim objServ As Servico
        For Each dr As DataRow In Registros.Tables(0).Rows
            objServ = CriarServico(dr)
            lista.Add(objServ)
        Next
        Return lista
    End Function
    Public Function CriarServico(ByVal row As DataRow) As Servico
        Dim objServ As New Servico
        Dim objDespDao As New DespachateDAO


        objServ.CodServ = CInt(row.Item("cod_serv"))
        objServ.Depachante = objDespDao.SelectDespId(CInt(row.Item("fk_cod_desp")))
        objServ.DescServ = CStr(row.Item("desc_serv"))
        objServ.ValorServ = CDec(row.Item("valor_serv"))


        Return objServ
    End Function

    Public Function Inserir(ByVal IServico As Servico) As Integer
        Dim registros As Integer
        Dim sql As New StringBuilder
        sql.Append("insert into tb_servico (")
        sql.Append(" fk_cod_desp, ")
        sql.Append(" desc_serv, ")
        sql.Append(" valor_serv ")
        sql.Append(") values (")
        sql.Append("" & IServico.Depachante.CodDesp & ",")
        sql.Append("'" & IServico.DescServ & "',")
        sql.Append("" & IServico.ValorServ.Replace(",", ".") & ")")
        registros = objAcessoDados.ExecutaSql(sql.ToString)
        Return registros
    End Function

    Public Function SelectServicoDesp(ByVal cod_desp As Integer) As Servico
        Dim objServ As Servico
        Dim registro As DataSet = New DataSet

        Dim sql As New StringBuilder
        Dim strWhere As String = " Where "
        sql.Append("select * from tb_servico ")
        sql.Append(strWhere & "fk_cod_desp = " & cod_desp)
        registro = objAcessoDados.ExecutaSqlRetorno(sql.ToString)
        Try
            Dim objServData As DataRow = registro.Tables(0).Rows(0)
            objServ = CriarServico(objServData)
            Return objServ
        Catch ex As Exception
            Return Nothing
        End Try
        Return Nothing
    End Function

    Public Function SelectServId(ByVal cod_serv As Integer) As Servico
        Dim objServ As New Servico
        Dim registro As DataSet = New DataSet

        Dim sql As New StringBuilder
        Dim strWhere As String = " Where "
        sql.Append("select * from tb_servico ")
        sql.Append(strWhere & "cod_serv = " & cod_serv)
        registro = objAcessoDados.ExecutaSqlRetorno(sql.ToString)
        Try
            Dim objServData As DataRow = registro.Tables(0).Rows(0)
            objServ = CriarServico(objServData)
            Return objServ
        Catch ex As Exception
            Return Nothing
        End Try
        Return Nothing
    End Function

    Public Function SelectServDesc(ByVal serv As Servico) As Servico
        Dim objServ As New Servico
        Dim registro As DataSet = New DataSet

        Dim sql As New StringBuilder
        Dim strWhere As String = " Where "
        sql.Append("select * from tb_servico ")
        sql.Append(strWhere & "desc_serv = '" & serv.DescServ.ToString & "'")
        registro = objAcessoDados.ExecutaSqlRetorno(sql.ToString)
        Try
            Dim objServData As DataRow = registro.Tables(0).Rows(0)
            objServ = CriarServico(objServData)
            Return objServ
        Catch ex As Exception
            Return Nothing
        End Try
        Return Nothing
    End Function

    Public Function Atualizar(ByVal serv As Servico) As Integer
        Dim registros As Integer
        Dim sql As New StringBuilder
        sql.Append("update tb_servico set ")
        sql.Append("desc_serv = '" & serv.DescServ.ToString & "', ")
        sql.Append("valor_serv = " & serv.ValorServ.Replace(",", "."))
        sql.Append(" where cod_serv = " & serv.CodServ)
        registros = objAcessoDados.ExecutaSql(sql.ToString)
        Return registros
    End Function

    Public Function Deleta(ByVal serv As Servico) As Integer
        Dim registros As Integer
        Dim sql As New StringBuilder

        sql.Append("delete from tb_servico ")
        sql.Append("where cod_serv = " & serv.CodServ & " ")
        registros = objAcessoDados.ExecutaSql(sql.ToString)

        Return registros
    End Function

End Class

